﻿--use PolePosition
GO

IF EXISTS (SELECT *  FROM sys.objects  WHERE object_id = OBJECT_ID(N'fn_DistributionPole_GetTopTypeString') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION fn_DistributionPole_GetTopTypeString

GO
CREATE FUNCTION fn_DistributionPole_GetTopTypeString
(
	  @DistributionPoleId int
	 ,@IsAsset bit
)
RETURNS varchar(MAX)
AS
BEGIN

	DECLARE @Join varchar(MAX)
	SET @Join = ''
	
	IF (@IsAsset = 1)
	BEGIN
		SELECT 
			@Join = @Join + TopType.[Name] + ' - '
		FROM tb_Utility_Asset_Distribution_TopsXPole XPole
		INNER JOIN tb_Distributions_TopTypes TopType
			ON TopType.Distributions_TopTypeId = XPole.TopTypeFk
		WHERE XPole.Utility_Asset_DistributionFk = @DistributionPoleId
	END	
	ELSE
	BEGIN
/*	
		SELECT 
			@Join = @Join + TopType.[Name] + ' - '
		FROM 
			tb_Vendor_Distribution_TopsXPole XPole
		INNER JOIN tb_Distributions_TopTypes TopType
			ON TopType.Distributions_TopTypeId = XPole.Vendor_Distribution_TopFk
		WHERE 
			XPole.Vendor_Distribution_PoleFk = @DistributionPoleId
*/

		SELECT 
			@Join = @Join + CASE
								WHEN us.SettingValue = '0' THEN toptype.Name --New Top type style display
								ELSE ISNULL(TopType.OldStyleName, 'N/A') --Old Type style display
							END + ' - '
		FROM 
			tb_Vendor_Distribution_TopsXPole XPole
		INNER JOIN tb_Distributions_TopTypes TopType
			ON TopType.Distributions_TopTypeId = XPole.Vendor_Distribution_TopFk
		LEFT JOIN tb_Vendor_Distribution_Poles poles
			ON poles.Vendor_Distribution_Poles_PoleId = @DistributionPoleId
		INNER JOIN tb_Vendor_Projects projects
			ON projects.Vendor_ProjectId = poles.ProjectFk
		INNER JOIN tb_Utility_Settings us
			ON us.UtilityFk = projects.UtilityFk
			AND us.SettingName = 'TopTypeDisplayStyle'
		WHERE 
			XPole.Vendor_Distribution_PoleFk = @DistributionPoleId	
		GROUP BY
			CASE
				WHEN us.SettingValue = '0' THEN toptype.Name --New Top type style display
				ELSE ISNULL(TopType.OldStyleName, 'N/A') --Old Type style display
			END 			
	END

	IF (@Join <> '' AND NOT @Join IS NULL)
		SET @Join = LEFT(@Join, LEN(@Join) - 2) 

	RETURN @Join

END

GO 